package sorm;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Vibhushan
 */
public class Table extends HashMap {

    private String tableName;
    private String idColumn = "id";
    private HashMap ownedBy = new HashMap();
    private boolean isLoaded = false;

    public Table() {
        super();
        //this.conn = new MySqlConnection();
        tableName = this.getClass().getName().replaceAll(this.getClass().getPackage().getName() + ".", "");
        tableName = tableName + "s";
        put("id", null);
    //System.out.println(forignTableName);
    }

    /***
     * 
     * @param tableName
     * This method should be called in the constructor of a class which extends Table.
     * argument tableName binds the child class with a table in the db
     */
    public void forTable(String tableName) {
        this.tableName = tableName;
    }

    /***
     * 
     * @return
     * returns any forign keys if invovled(Work in progress)
     */
    public HashMap getOwnedBy() {
        return ownedBy;
    }

    /**
     * This method should be called in the constructor of a class which extends Table.
     * @param idColumn
     * argument idColumn binds the child class id column with an id column of the table in the db
     */
    public void idColumn(String idColumn) {
        this.idColumn = idColumn;
    }

    /**
     * 
     * @param tableName
     * In Progress
     */
    public void hasMany(String tableName) {

    }

    /**
     * This method should be called in the constructor of a class which extends Table.
     * This method established relations of two tables for example order is ownedBy a user.
     * @param forignTableName
     * Table name to which a forigh key might be referring
     * @param forignKeyColumnName
     *Column name which has the forign key 
     */
    public void ownedBy  (String forignTableName, String forignKeyColumnName) throws Exception{
        try {
            Class c = Class.forName(forignTableName);
            Table t = (Table) c.newInstance();
            //String className = t.getClass().getName().replaceAll(t.getClass().getPackage().getName() + ".", "");
            //put(forignTableName.toLowerCase(), t);
            ownedBy.put(forignKeyColumnName.toLowerCase(), t);
        } catch (ClassNotFoundException ex) {
            throw ex;
        } catch (InstantiationException ex) {
            throw ex;
        } catch (IllegalAccessException ex) {
            throw ex;
        }

    }

    public Table ownedBy(String className) {
        return (Table) ownedBy.get(className.toLowerCase());
    }

    /**
     * 
     * @param id
     * id of a row to be loaded
     * @param columnNames
     * columns to be loaded in a semi colon seperated list.
     * @return
     */
    public boolean loadColumns (String id, String columnNames)throws SQLException {
        boolean result = false;
        MySqlConnection conn = ConnectionPool.leaseConnection();
        isLoaded = true;
        try {
            String sql = "";
            if (columnNames != null && !"".equals(columnNames)) {
                sql = "select " + idColumn + " as id, " + columnNames + " from " + tableName + " where " +
                        idColumn + "=" + id;
                ResultSet rs = conn.executeQuery(sql);
                ResultSetMetaData rsm = rs.getMetaData();
                if (rs.next()) {
                    for (int i = 1; i <= rsm.getColumnCount(); i++) {
                        String columnName = rsm.getColumnName(i).toLowerCase();
                        put(columnName.toLowerCase(), rs.getString(columnName));
                        if (ownedBy.containsKey(columnName)) {
                            String fTableName = ownedBy.get(columnName).toString();
                            Table t = ((Table) ownedBy.get(columnName));
                            t.loadColumns(rs.getString(columnName), null);
                            ownedBy.put(fTableName, t);
                        }
                    }
                }
            } else {
                put("id", id);
            }
            result = true;
        } catch (SQLException ex) {
            throw ex;
        } finally {
            ConnectionPool.releaseConnection(conn);
        }

        return result;
    }

    /**
     * 
     * @return
     * returns names of all the columns of a table represented by that class.
     */
    public ArrayList getColumnNames() throws SQLException{
        ArrayList result = new ArrayList();
        MySqlConnection conn = ConnectionPool.leaseConnection();
        try {
            ResultSet rs = conn.executeQuery("desc " + tableName);
            while (rs.next()) {
                result.add(rs.getString("field"));
            }
        } catch (SQLException ex) {
            throw ex;
        } finally {
            ConnectionPool.releaseConnection(conn);
        }
        return result;
    }

    /**
     * 
     * @param whereClause
     * Where clause of a select query without word "where"
     * @param columnNames
     * Columns to be loaded
     * @return
     * Array list of the results, the elements in the array list are of type of the caller class.
     */
    public ArrayList select(String whereClause, String columnNames) throws SQLException,Exception{
        ArrayList results = new ArrayList();
        Class c = this.getClass();
        MySqlConnection conn = ConnectionPool.leaseConnection();
        try {
            String sql = "";
            if (columnNames != null && !"".equals(columnNames)) {
                sql = "select " + columnNames +"," + idColumn + " as id  from " + tableName +(whereClause!=null && !"".equals(whereClause)?" where " +
                        whereClause:"");
            } else {
                sql = "select " + idColumn + " as id from " + tableName + (whereClause!=null && !"".equals(whereClause)?" where " +
                        whereClause:"");
            }
            
            ResultSet rs = conn.executeQuery(sql);
            ResultSetMetaData rsm = rs.getMetaData();
            while (rs.next()) {
                Table t = (Table) c.newInstance();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    String columnName = rsm.getColumnName(i).toLowerCase();
                    t.put(columnName.toLowerCase(), rs.getString(columnName));
                    if (ownedBy.containsKey(columnName)) {
                        String fTableName = ownedBy.get(columnName).toString();
                        Table p = ((Table) ownedBy.get(columnName));
                        p.loadColumns(rs.getString(columnName), null);
                        ownedBy.put(fTableName, p);
                    }
                }
                results.add(t);
            }

        } catch (SQLException ex) {
            throw ex;
        } catch (Exception ex) {
            throw ex;
        } finally {
            ConnectionPool.releaseConnection(conn);
        }

        return results;
    }

    /**
     * 
     * @param columnName
     * Column name to be inserted or updated
     * @param columnVal
     * Column value to be inserted or updated
     */
    public void put(String columnName, String columnVal) {
        super.put(columnName.toLowerCase(), columnVal);
    }

    public Object get(String columnName) throws SQLException{
        if (!containsKey(columnName.toLowerCase())) {
            MySqlConnection conn = ConnectionPool.leaseConnection();
            try {
                ResultSet rs = conn.executeQuery("select " + columnName + " from " + tableName + " where " + idColumn + "=" + get("id"));
                if (rs.next()) {
                    put(columnName.toLowerCase(), rs.getString(columnName));
                } else {
                    put(columnName.toLowerCase(), null);
                }

            } catch (SQLException ex) {
                throw ex;
            } finally {
                ConnectionPool.releaseConnection(conn);
            }
        }
        return super.get(columnName.toLowerCase());
    }

    /***
     * if object ever called load columns method then this method will update that specific row otherwise will insert a new row
     * @return
     */
    public boolean save() throws SQLException{
        boolean result = false;
        String sql = "";
        String sql2 = "";
        String tempColumnName = "";
        Object chkId = get("id");
        MySqlConnection conn = ConnectionPool.leaseConnection();
        try {
            if (chkId == null || "".equals(chkId)) {

                Iterator columnNames = keySet().iterator();
                sql += "insert into " + tableName + "(";
                sql2 += "values(";
                while (columnNames.hasNext()) {
                    tempColumnName = columnNames.next().toString();
                    if (!tempColumnName.equals("id")) {
                        sql += tempColumnName + ",";
                        sql2 += "'" + get(tempColumnName) + "',";
                    }
                }
                sql = sql.substring(0, sql.length() - 1) + ")";
                sql2 = sql2.substring(0, sql2.length() - 1) + ")";
                sql += sql2;
               // System.out.println(sql);
                int id = conn.executeInsert(sql);
                put("id", String.valueOf(id));

            } else {
                Iterator columnNames = keySet().iterator();
                sql += "update " + tableName + " set ";

                while (columnNames.hasNext()) {
                    tempColumnName = columnNames.next().toString();
                    if (!tempColumnName.equals("id")) {
                        sql += tempColumnName + "=" + "'" + get(tempColumnName) + "',";
                    }

                }
                sql = sql.substring(0, sql.length() - 1) + "where " + idColumn + " = " + get(idColumn.toLowerCase());
                System.out.println(sql);
                conn.executeUpdate(sql);

            }
        } catch (SQLException ex) {
            Logger.getLogger(Table.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            ConnectionPool.releaseConnection(conn);
        }
        return result;
    }
}
